package com.hzdl.library.poiuntil;

import java.io.*;
import java.util.*;

import com.hzdl.library.domain.LendList;
import com.hzdl.library.util.JDBCUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.hssf.usermodel.*;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class OpenWorkBook {

    //打开文件
//    public static void main(String args[]) throws Exception {
////        File file = new File("openworkbook.xlsx");
////        FileInputStream fIP = new FileInputStream(file);
////        //Get the workbook instance for XLSX file
////        HSSFWorkbook workbook = new HSSFWorkbook(fIP);
////        if (file.isFile() && file.exists()) {
////            System.out.println(
////                    "openworkbook.xlsx file open successfully.");
////        } else {
////            System.out.println(
////                    "Error to open openworkbook.xlsx file.");
////        }
//
//        //Create blank workbook
//        HSSFWorkbook workbook = new HSSFWorkbook();
//        //Create a blank sheet
//        HSSFSheet spreadsheet = workbook.createSheet(
//                " Employee Info ");
//        //Create row object
//        HSSFRow row;
//        //This data needs to be written (Object[])
//        Map < String, Object[] > empinfo =new TreeMap < String, Object[] >();
//        empinfo.put( "1", new Object[] {"EMP ID", "EMP NAME", "DESIGNATION" });
//        empinfo.put( "2", new Object[] {"tp01", "Gopal", "Technical Manager" });
//        empinfo.put( "3", new Object[] {"tp02", "Manisha", "Proof Reader" });
//        empinfo.put( "4", new Object[] {"tp03", "Masthan", "Technical Writer" });
//        empinfo.put( "5", new Object[] {"tp04", "Satish", "Technical Writer" });
//        empinfo.put( "6", new Object[] {"tp05", "Krishna", "Technical Writer" });
//        //Iterate over data and write to sheet
//        Set< String > keyid = empinfo.keySet();
//        int rowid = 0;
//        for (String key : keyid)
//        {
//
//            row = spreadsheet.createRow(rowid++);
//            Object [] objectArr = empinfo.get(key);
//            int cellid = 0;
//            for (Object obj : objectArr)
//            {
//                Cell cell = row.createCell(cellid++);
//                cell.setCellValue((String)obj);
//            }
//        }
//        //Write the workbook in file system
//        FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
//        workbook.write(out);
//        out.close();
//        System.out.println("Writesheet.xlsx written successfully" );
//
//
//    }
   //
    public File createFile(String path) {

        String[] split = path.split("\\.");


        //System.out.println(Arrays.toString(split));
        if (split[1].equals("xlsx")||split[1].equals("xls"));

        File file = new File(path);
        return file;
    }


    //读取数据
    public void readMysql(){
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

        File file = createFile("log.xlsx");
        HSSFWorkbook workbook = new HSSFWorkbook();
        Map< String, Object[] > empInfo = new TreeMap< String, Object[] >();

        empInfo.put( "1", new Object[] {"sernum", "book_id", "reader_id","lend_date", "back_date"});
        String sql = "select * from lend_list";
        List<LendList> listForLog = template.query(sql, new BeanPropertyRowMapper<LendList>(LendList.class));

       for (int i = 0; i < listForLog.size(); i++) {
           empInfo.put(i + 2 +"",new Object[]{listForLog.get(i).getBook_id(),listForLog.get(i).getBook_id(),listForLog.get(i).getReader_id(),listForLog.get(i).getLend_date(),listForLog.get(i).getBack_date()});

        }
    }


    //static HSSFRow row;

    public void  writeToSql() throws IOException {

        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

        HSSFRow row;
        FileInputStream fis = new FileInputStream(new File("lend_log.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator <Row> rowIterator = spreadsheet.iterator();
        for (int i = 1; i < spreadsheet.getLastRowNum()+1; i++) {
            double serNum = spreadsheet.getRow(i).getCell(0).getNumericCellValue();
            double book_id = spreadsheet.getRow(i).getCell(1).getNumericCellValue();
            double reader_id = spreadsheet.getRow(i).getCell(2).getNumericCellValue();
            String lend_date = spreadsheet.getRow(i).getCell(3).getStringCellValue();
            String back_date = spreadsheet.getRow(i).getCell(4).getStringCellValue();
            if ("null".equals(back_date)){
                String sql = "insert into lend_list values(?,?,?,?,null)";
                template.update(sql,serNum,book_id,reader_id,lend_date);
            }else {
                String sql = "insert into lend_list values(?,?,?,?,?)";
                template.update(sql,serNum,book_id,reader_id,lend_date,back_date);
            }
        }
        try {
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void mm() throws IOException {
        writeToSql();
    }

}

